﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using BTN04.DTO;
using System.Data.SqlClient;

namespace BTN04.DAO
{
    public class SanPhamDAO
    {
        //lay san pham theo laoi sna pham
        public List<SanPhamDTO> DanhSachSanPhamTheoLoai(int _maLoai)
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT * FROM SANPHAM WHERE MaLoaiSanPham = " + _maLoai.ToString();
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
                spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
                spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
                spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                _danhSach.Add(spDTO);
            }
            return _danhSach;
        }
        //lay san pham theo loai san pham Phan Trang
        public DataTable DanhSachSanPhamTheoLoaiPhanTrang(int _maLoai,int page,int size)
        {
            DataTable dt = new DataTable();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT * FROM SANPHAM WHERE MaLoaiSanPham = " + _maLoai.ToString();
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(comand);
            DataSet ds = new DataSet();
            adapter.Fill(ds, page, size, "SanPham");
            dt = ds.Tables["SanPham"];
            return dt;            
        }//end lay sna pham theo loai san pham phan trang
        //lay so luong san pham theo loai
        public int SoLuongSanPhamTheoLoaiSanPham(int maLoai)
        {
            DataTable dt = new DataTable();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT * FROM SANPHAM WHERE MaLoaiSanPham = " + maLoai.ToString();
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(comand);           
            adapter.Fill(dt);           
            return dt.Rows.Count;    
        }
        public List<SanPhamDTO> DanhSachSanPhamCungLoai(int _maLoai)
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT Top 6 MaSanPham,TenSanPham,GiaBan,HinhAnh,MaKhuyenMai,MaLoaiSanPham FROM SANPHAM WHERE MaLoaiSanPham = " + _maLoai.ToString();
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
               // spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
               // spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
               // spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                //spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                _danhSach.Add(spDTO);
            }
            return _danhSach;
        }


        public List<SanPhamDTO> SanPhamXemNhieuTrongNgay()
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT TOP 10 * FROM SANPHAM ORDER BY SoLuongXem DESC";
            //str += " Limit 15";
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
                spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
                spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
                spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                _danhSach.Add(spDTO);
            }
            return _danhSach;
        }

        public List<SanPhamDTO> SanPhamNew()
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT TOP 5 * FROM SANPHAM ";
            str += " ORDER BY NgayNhap DESC ";
            // [ ASC | DESC ] 
           // str += " LIMIT 5";
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
                spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
                spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
                spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                spDTO.MoTa = read["MoTa"].ToString();                
                _danhSach.Add(spDTO);
            }
            cnn.Close();
            return _danhSach;
        }

        public DataTable SanPhamTop10SoLuongBan()
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT TOP 10 sp.TenSanPham,Sum(Tam.SoLuongMua) as SoLuongBan";
            str += " FROM SANPHAM sp,(SELECT MaSanPham,SoLuongMua FROM ChiTietHoaDon ) as Tam ";
            str +=" WHERE sp.MaSanPham = Tam.MaSanPham ";
            str += " GROUP BY sp.TenSanPham";
            str += " ORDER BY SoLuongBan DESC ";
           
            // str += " ORDER BY NgayNhap DESC";
            // str += " LIMIT 5";
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            DataTable dt = new DataTable();
            SqlDataAdapter adapte = new SqlDataAdapter(comand);
            adapte.Fill(dt);
            cnn.Close();
            return dt;
        }

        public DataTable SanPhamTop10SoLuongBanTheoLoaiSanPham(int MaLoaiSanPham)
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT TOP 10 sp.TenSanPham,Sum(Tam.SoLuongMua) as SoLuongBan ,sp.GiaBan";
            str += " FROM SANPHAM sp,(SELECT MaSanPham,SoLuongMua FROM ChiTietHoaDon ) as Tam ";
            str += " WHERE sp.MaSanPham = Tam.MaSanPham AND sp.MaLoaiSanPham = " + MaLoaiSanPham.ToString();
            str += " GROUP BY sp.TenSanPham,sp.GiaBan";
            str += " ORDER BY SoLuongBan DESC ";

            // str += " ORDER BY NgayNhap DESC";
            // str += " LIMIT 5";
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            DataTable dt = new DataTable();
            SqlDataAdapter adapte = new SqlDataAdapter(comand);
            adapte.Fill(dt);
            return dt;
        }


        public DataTable LaySanPhamTheoMa(string _maSanPham)
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT *";
            str += " FROM SANPHAM sp,NhaSanXuat nsx,LoaiSanPham lsp,XuatSu xs ";
            str += " WHERE sp.MaNhaSanXuat = nsx.MaNhaSanXuat AND sp.MaLoaiSanPham = lsp.MaLoaiSanPham AND sp.MaXuatSu = xs.MaXuatSu AND sp.MaSanPham = " + _maSanPham;
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(comand);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }
        //Tim kiem san pham
        public DataTable  TimKiemThuong(SanPhamCrt spCrt)
        {
            SqlCommand cmd = new SqlCommand();
            string dkTen = "1=1";
            string dkLoaiSanPham = "1=1";
            string dkGia = "1=1";

            if (spCrt.ChkTen == true)
            {
                dkTen = " sp.TenSanPham like '%" + spCrt.TenSanPham+ "%' ";
            }
            if (spCrt.ChkLoaiSanPham == true)
            {
                dkLoaiSanPham = " sp.MaLoaiSanPham = " + spCrt.MaLoaiSanPham.ToString ();
            }

            if (spCrt.ChkGia == true)
            {
                dkGia = " sp.GiaBan between "+spCrt.GiaTu.ToString()+" and "+spCrt.GiaDen.ToString ();
            }

            string dkWhere = " Where ";
            dkWhere += dkTen;
            dkWhere += " And " + dkLoaiSanPham;
            dkWhere += " And " + dkGia;

            string sql = "Select *";
            sql += " FROM SANPHAM sp,NhaSanXuat nsx,LoaiSanPham lsp ";
            sql += dkWhere;
            sql+= " And sp.MaNhaSanXuat = nsx.MaNhaSanXuat AND sp.MaLoaiSanPham = lsp.MaLoaiSanPham ";
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            cnn.Open();
            cmd = new SqlCommand(sql ,cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            cnn.Close();
            return dt;
        }
        //tim kiem nag cao
        public DataTable TimKiemNangCao(string TenSanPham, int GiaTu, int GiaDen, int MaNhaSanXuat, int MaLoaiSanPham, int MaXuatSu, int page, int size)
        {
            string sql = "SELECT * FROM SanPham WHERE TenSanPham like '%" +TenSanPham +"%' ";
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);

            cnn.Open();
            if (MaLoaiSanPham != 0)
                sql += " AND MaLoaiSanPham =" + MaLoaiSanPham;
            if (MaNhaSanXuat != 0)
                sql += " AND MaNhaSanXuat=" + MaNhaSanXuat;
            if (MaXuatSu != 0)
                sql += " AND MaXuatSu=" + MaXuatSu;           
            if(GiaTu != 0 && GiaDen != 0)
                sql += " AND GiaBan  between " + GiaTu + " AND " + GiaDen;
            else
            {
                if (GiaTu == 0 && GiaDen!= 0)
                {
                    sql += " AND GiaBan < " + GiaDen;
                }
                if (GiaTu != 0 && GiaDen == 0)
                {
                    sql += " AND GiaBan > " + GiaTu;
                }
            }
            SqlCommand comand = new SqlCommand(sql, cnn);
            SqlDataAdapter Adapter = new SqlDataAdapter(comand);
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            Adapter.Fill(ds,page,size,"SanPham");
            dt = ds.Tables["SanPham"];
            return dt;            
        }

        public int SoLuongSanPhamTimKiemNangCao(string TenSanPham, int MaXuatSu,int MaLoaiSanPham,int MaNhaSanXuat,int GiaTu,int GiaDen)
        {
            string sql = "SELECT * FROM SanPham WHERE TenSanPham like '%" + TenSanPham + "%' ";
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);

            cnn.Open();
            if (MaLoaiSanPham != 0)
                sql += " AND MaLoaiSanPham =" + MaLoaiSanPham;
            if (MaNhaSanXuat != 0)
                sql += " AND MaNhaSanXuat=" + MaNhaSanXuat;
            if (MaXuatSu != 0)
                sql += " AND MaXuatSu=" + MaXuatSu;
            if (GiaTu != 0 && GiaDen != 0)
                sql += " AND GiaBan  between " + GiaTu + " AND " + GiaDen;
            else
            {
                if (GiaTu == 0 && GiaDen != 0)
                {
                    sql += " AND GiaBan < " + GiaDen;
                }
                if (GiaTu != 0 && GiaDen == 0)
                {
                    sql += " AND GiaBan > " + GiaTu;
                }
            }
            SqlCommand comand = new SqlCommand(sql, cnn);
            SqlDataAdapter Adapter = new SqlDataAdapter(comand);
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            Adapter.Fill(dt);           
            return dt.Rows.Count;            
        }
        //end tim kiem nang cao
        public int SoLuongSanPham()
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            cnn.Open();
            string sql = "SELECT count(*) as sum FROM SanPham";
            SqlCommand comand = new SqlCommand(sql,cnn);
            SqlDataReader read = comand.ExecuteReader();
            read.Read();
            int kq = Convert.ToInt32(read["sum"]);
            return kq;
        }

        public List<SanPhamDTO> ThucThiStore_DataSet(string StoredProcedure, params SqlParameter[] Parameters)
        {
            
            SqlConnection Conn = new SqlConnection(DataProvider.ConnectionString);
            Conn.Open();
            SqlCommand comand = new SqlCommand();
            comand.Connection = Conn;
            comand.CommandType = CommandType.StoredProcedure;
            comand.CommandText = StoredProcedure;
           //SqlParameter p1 = new SqlParameter("@")
            if (Parameters != null)
            {
                comand.Parameters.Clear();
                comand.Parameters.AddRange(Parameters);
            }
            DataSet ds = new DataSet();
            List<SanPhamDTO> lst = new List<SanPhamDTO>();
            
            try
            {
                //Mở kết nối
                //Conn.Open();
                //da.Fill(ds);
                SqlDataReader read = comand.ExecuteReader();
                while (read.Read())
                {
                    SanPhamDTO spDTO = new SanPhamDTO();
                    spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                    spDTO.TenSanPham = read["TenSanPham"].ToString();
                    spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
                    spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
                    spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                    spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                    spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                    spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                    spDTO.Hinh = read["HinhAnh"].ToString();
                    spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                    spDTO.MoTa = read["MoTa"].ToString();
                    lst.Add(spDTO);
                }
            }
            finally
            {
                // Đóng kết nối
                if (Conn.State == ConnectionState.Open)
                    Conn.Close();
                Conn.Dispose();
            }
            return lst;
        }

        public List<SanPhamDTO> StoreToDataSet(int currPage, int recodperpage, int Pagesize)
        {
            DataSet dts = new DataSet();
            
            SqlParameter[] arrParam = {
                new SqlParameter("@currPage", SqlDbType.Int),
                new SqlParameter("@recodperpage", SqlDbType.Int),
                new SqlParameter("@Pagesize", SqlDbType.Int)
                };
            arrParam[0].Value = currPage;
            arrParam[1].Value = recodperpage;
            arrParam[2].Value = Pagesize;
            return ThucThiStore_DataSet("spCustomers_PhanTrang", arrParam);
        }
        //lay thông tin san pham theo ma
        public static SanPhamDTO LaySanPhamTheoMa(int masp)
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT * FROM SANPHAM Where MaSanPham=" + masp.ToString();

            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            SanPhamDTO spDTO = new SanPhamDTO();
            while (read.Read())
            {
                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
                spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
                spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
                spDTO.SoLuongTon = int.Parse(read["SoLuongTon"].ToString());
                spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                spDTO.MoTa = read["MoTa"].ToString();
            }
            cnn.Close();
            return spDTO;
        }
        //end lay thong tin san pham theo ma
        //ham update lai so luong ton
        public int CapNhatSoLuongTon(int masp, int soluong)
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "Update SANPHAM Set SoLuongTon=SoLuongTon-"+soluong .ToString ()+" Where MaSanPham=" + masp.ToString();
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            int kq= comand.ExecuteNonQuery ();
            cnn.Close();
            return  kq;
        }
        //end update so luong ton
        //Hien thi san pham len girlvied thuc hien thao tac them xoa sua

        public List<SanPhamDTO> HienThiChiTietToanBoSanPham()
        {
            List<SanPhamDTO> _danhSach = new List<SanPhamDTO>();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT MaSanPham,TenSanPham,lsp.TenLoaiSanPham,nsx.TenNhaSanXuat,xs.TenXuatSu,GiaBan,SoLuongXem,HinhAnh,MoTa";
            str += " FROM SANPHAM sp,NhaSanXuat nsx,LoaiSanPham lsp,XuatSu xs ";
            str += " WHERE sp.MaNhaSanXuat = nsx.MaNhaSanXuat AND sp.MaLoaiSanPham = lsp.MaLoaiSanPham AND sp.MaXuatSu = xs.MaXuatSu";

            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
                spDTO.TenLoaiSanPham = read["TenLoaiSanPham"].ToString();
                spDTO.TenNhaSanXuat = read["TenNhaSanXuat"].ToString();
                spDTO.TenXuatSu = read["TenXuatSu"].ToString();
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MoTa = read["MoTa"].ToString();
                _danhSach.Add(spDTO);


            }
            cnn.Close();
            return _danhSach;
        }
        //Hien thi chi chi tiet 1 san pham theo Ma San Pham
        public SanPhamDTO HienThiChiTietSanPhamTheoMa(int ma)
        {
            SanPhamDTO spDTO = new SanPhamDTO();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "SELECT MaSanPham,TenSanPham,sp.MaLoaiSanPham,lsp.TenLoaiSanPham,sp.MaNhaSanXuat,sp.MaXuatSu,sp.MaKhuyenMai,sp.MaTinhTrangSanPham,trsp.TenTinhTrangSanPham,nsx.TenNhaSanXuat,xs.TenXuatSu,GiaBan,SoLuongXem,HinhAnh,MoTa";
            str += " FROM SANPHAM sp,NhaSanXuat nsx,LoaiSanPham lsp,XuatSu xs,TinhTrangSanPham trsp";
            str += " WHERE sp.MaNhaSanXuat = nsx.MaNhaSanXuat AND sp.MaLoaiSanPham = lsp.MaLoaiSanPham AND sp.MaXuatSu = xs.MaXuatSu and sp.MaTinhTrangSanPham=trsp.MaTinhTrangSanPham and MaSanPham=" + ma;

            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataReader read = comand.ExecuteReader();
            while (read.Read())
            {

                spDTO.MaSanPham = int.Parse(read["MaSanPham"].ToString());
                spDTO.TenSanPham = read["TenSanPham"].ToString();
                spDTO.TenLoaiSanPham = read["TenLoaiSanPham"].ToString();
                spDTO.MaLoaiSanPham = int.Parse(read["MaLoaiSanPham"].ToString());
                spDTO.MaTinhTrangSanPham = int.Parse(read["MaTinhTrangSanPham"].ToString());
                spDTO.TenNhaSanXuat = read["TenNhaSanXuat"].ToString();
                spDTO.TenTinhTrangSanPham = read["TenTinhTrangSanPham"].ToString();
                spDTO.TenXuatSu = read["TenXuatSu"].ToString();
                spDTO.GiaBan = int.Parse(read["GiaBan"].ToString());
                // spDTO.SoLuongTon = int.Parse(read["SoLuongTon"].ToString());
                spDTO.MaXuatSu = int.Parse(read["MaXuatSu"].ToString());
                spDTO.MaKhuyenMai = int.Parse(read["MaKhuyenMai"].ToString());
                spDTO.MaNhaSanXuat = int.Parse(read["MaNhaSanXuat"].ToString());
                spDTO.SoLuongXem = int.Parse(read["SoLuongXem"].ToString());
                spDTO.Hinh = read["HinhAnh"].ToString();
                spDTO.MoTa = read["MoTa"].ToString();



            }
            cnn.Close();
            return spDTO;
        }
        //Xoa 1 san pham dua vao ma
        public int XoaMotSanPhamDuaVaoMa(int ma)
        {

            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "Update SanPham set MaTinhTrangSanPham=1 where MaSanPham=" + ma;

            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            int kq = comand.ExecuteNonQuery();
            cnn.Close();
            return kq;

        }
        //Cap nhat thong tin 1 san Pham
        public int CapNhatMotSanPham(SanPhamDTO spDto)
        {

            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = "Update SanPham set TenSanPham='" + spDto.TenSanPham + "'";
            str += ",MaLoaiSanPham=" + spDto.MaLoaiSanPham + ",MaNhaSanXuat=" + spDto.MaNhaSanXuat + ",MaTinhTrangSanPham=" + spDto.MaTinhTrangSanPham;
            str += ",GiaBan=" + spDto.GiaBan + /*",SoLuongTon=" + spDto.SoLuongTon +*/ ",SoLuongXem=" + spDto.SoLuongXem + ",MaXuatSu=" + spDto.MaXuatSu;
            str += ",HinhAnh='" + spDto.Hinh + "',MaKhuyenMai=" + spDto.MaKhuyenMai + ",MoTa='" + spDto.MoTa + "'" + "where MaSanPham=" + spDto.MaSanPham;


            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            int kq = comand.ExecuteNonQuery();
            cnn.Close();
            return kq;

        }
        //Thêm 1 san pham
        public int ThemMotSanPham(SanPhamDTO spDto)
        {

            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str = string.Format("Insert into SanPham(TenSanPham,MaLoaiSanPham,MaNhaSanXuat,MaTinhTrangSanPham,GiaBan,SoLuongXem,MaXuatSu,HinhAnh,MaKhuyenMai,MoTa) values('{0}',{1},{2},{3},{4},{5},{6},'{7}',{8},'{9}',)", spDto.TenSanPham, spDto.MaLoaiSanPham, spDto.MaNhaSanXuat, spDto.MaTinhTrangSanPham, spDto.GiaBan, spDto.SoLuongXem, spDto.MaXuatSu, spDto.Hinh, spDto.MaKhuyenMai, spDto.MoTa);
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            int kq = comand.ExecuteNonQuery();
            cnn.Close();
            return kq;
        }

        public DataTable Fill(int page,int size)
        {
            SqlConnection con = new SqlConnection(DataProvider.ConnectionString);
            con.Open();
            
            string str = "select * from SanPham order by SoLuongXem";
            SqlCommand cmd = new SqlCommand(str, con);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            da.Fill(ds, page, size, "SanPham");
            dt = ds.Tables["SanPham"];
            con.Close();
            return dt;
        }
    }
}
